
Imports System.Data.OleDb
Imports DTO
Public Class HocSinhDAO
    Inherits DataProviderDAO

    'lay danh sach HS 
    Public Function DanhSachHS() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = Me.ConnectionData()
        Dim strSQL As String
        strSQL = "Select HS.MaHS, HS.HoTen, HS.NgaySinh, HS.DiaChi, HS.Email, HS.GioiTinh, LH.TenLH " + _
                 "From HocSinh HS, LopHoc LH, NamHoc NH " + _
                 "Where HS.MaLH = LH.MaLH and LH.MaNam = NH.MaNH and LH.MaLH <> 11 and NH.Nam = " + Year(Now).ToString()
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    'lay danh sach HS chua duoc xep lop
    Public Function DSHSChuaXep() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = Me.ConnectionData()
        Dim strSQL As String
        strSQL = "Select HS.MaHS, HS.HoTen, HS.NgaySinh, HS.DiaChi, HS.Email, HS.GioiTinh From HocSinh HS Where HS.MaLH =11"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    ' them hoc sinh `
    Public Sub Them(ByVal hs As HocSinhDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData

        Dim strSQL As String
        strSQL = "Insert into HocSinh (HoTen, NgaySinh, DiaChi, Email, GioiTinh, MaLH) " + _
                 "values('" + hs.HoTen + "','" + hs.NgaySinh.ToString() + "','" + hs.DiaChi + "','" + hs.Email + "','" + hs.GioiTinh + "','" + hs.MaLH.ToString() + "')"

        Dim cm As New OleDbCommand(strSQL, cn)
        cm.ExecuteNonQuery()

        strSQL = "Select @@IDENTITY"
        cm = New OleDbCommand(strSQL, cn)

        hs.MaHS = cm.ExecuteScalar()

        cn.Close()
    End Sub


    ' Xoa Hoc Sinh
    Public Sub Xoa(ByVal hs As HocSinhDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim strSQL As String
        strSQL = "Update HocSinh set MaLH= 11 where MaHS= " + hs.MaHS.ToString()
        Dim cm As New OleDbCommand(strSQL, cn)
        cm.ExecuteNonQuery()
        cn.Close()
    End Sub

    'Sua Hoc Sinh
    Public Sub Sua(ByVal hs As HocSinhDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim strSQL As String
        strSQL = "Update HocSinh set HoTen = '" + hs.HoTen + "', NgaySinh = '" + hs.NgaySinh + "', " + _
        "DiaChi = '" + hs.DiaChi + "', Email = '" + hs.Email + "', GioiTinh = '" + hs.GioiTinh + "'," + _
        " MaLH = '" + hs.MaLH.ToString() + "' where MaHS= " + hs.MaHS.ToString()
        Dim cm As New OleDbCommand(strSQL, cn)
        cm.ExecuteNonQuery()
        cn.Close()
    End Sub

    'cap nhat : xep lop
    Public Sub CapNhat(ByVal maHS As Integer, ByVal maLH As Integer)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim strSQL As String
        strSQL = "Update HocSinh set MaLH = " + maLH.ToString() + " where MaHS= " + maHS.ToString()
        Dim cm As New OleDbCommand(strSQL, cn)
        cm.ExecuteNonQuery()
        cn.Close()
    End Sub


    'lay DSHS de Nhap Diem
    Public Function DSHSNhapDiem(ByVal MaLH As Integer) As DataTable
        Dim dt As New DataTable
        Dim strSQL As String
        strSQL = "SELECT  HS.MaHS, HS.HoTen " + _
                "FROM HOCSINH HS, LOPHOC LH, NAMHOC NH " + _
                 "WHERE HS.MaLH = LH.MaLH and LH.MaNam = NH.MaNH and HS.MaLH= " + MaLH.ToString() + " and NH.Nam = " + Year(Now).ToString()
        dt = Execute(strSQL)
        Return dt
    End Function


    'Oanh
    ' Ham Tra cuu diem hoc sinh cua giao vien
    'Tham so dau vao: maLop, maNH, maHK, maLKT, maMH
    Public Function TraCuuHS_GVien(ByVal maLop As Integer, ByVal maNH As Integer, ByVal maHK As Integer, ByVal maLKT As Integer, ByVal maMH As Integer) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = Me.ConnectionData()
        Dim strSQL As String
        strSQL = " select  hs.HoTen, hs.NgaySinh, d.Diem" + _
                    " from HocSinh hs, Diem d, LopHoc lh, MonHoc mh" + _
                    " where(d.MaHS = hs.MaHS)" + _
                    " and hs.MaLH= lh.MaLH " + _
                    " and d.MaMH= mh.MaMH " + _
                    " and lh.MaLH=  " + maLop.ToString() + _
                    " and d.HocKy= " + maHK.ToString() + _
                    " and lh.MaNam=  " + maNH.ToString() + _
                    " and d.MaLKT = " + maLKT.ToString() + _
                    " and mh.MaMH = " + maMH.ToString()
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function
    'Oanh
    'Ham tra cuu hoc sinh
    'Tham so dau vao : hoTen, email, maLop, maNH, maHK, maLKT
    Public Function TraCuuHS(ByVal hoTen As String, ByVal email As String, ByVal maLop As Integer, ByVal maNH As Integer, ByVal maHK As Integer, ByVal maLKT As Integer) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = Me.ConnectionData()
        Dim strSQL As String
        strSQL = " select hs.HoTen, mh.TenMH, lkt.TenLKT, d.Diem" + _
                    " from Diem d, HocSinh hs, LopHoc lh, MonHoc mh, LoaiKiemTra lkt" + _
                    " where  hs.HoTen LIKE '%" + hoTen + "%' " + _
                    " and hs.Email LIKE '%" + email + "%' " + _
                    " and mh.MaMH= d.MaMH " + _
                    " and d.MaHS=hs.MaHS " + _
                    " and d.MaMH=mh.MaMH  " + _
                    " and d.MaLKT=lkt.MaLKT " + _
                    " and lh.MaLH =hs.MaLH " + _
                    " and lh.MaLH=  " + maLop.ToString() + _
                    " and d.HocKy= " + maHK.ToString() + _
                    " and lh.MaNam=  " + maNH.ToString() + _
                    " and d.MaLKT = " + maLKT.ToString()
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(strSQL, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function


End Class

